﻿using BrnMall.Manager.Domain.AddressBook;
using PetaPoco;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static BrnMall.Manager.Domain.AddressBook.Clients;

namespace BrnMall.Manager.Manager.AddressBook
{
    /// <summary>
    /// 我的客户操作
    /// </summary>
    public static class ClientManager
    {
        public static bool Insert(Clients model)
        {
            using (var db = new QDMallDatabase())
            {
                db.Insert(model);
            }
            return model.Id > 0;
        }

        public static Page<ClientView> GetClientListPage(int staffid, int page, int pageSize = 10)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("select * from crm_Client where Staffid={0}", staffid);
            sql.Append(" order by addtime desc");
            using (var db = new QDMallDatabase())
            {
                return db.Page<ClientView>(page, pageSize, sql.ToString());
            }
        }
        public static Page<Clients> GetClientListPages(int staffid, int page, int pageSize = 10)
        {
            var sql = new StringBuilder();
            sql.AppendFormat("select * from crm_Client where Staffid={0}", staffid);
            sql.Append(" order by addtime desc");
            using (var db = new QDMallDatabase())
            {
                return db.Page<Clients>(page, pageSize, sql.ToString());
            }
        }
        public static Page<ClientView> GetListPageAlll(int staffid, int page, int pageSize)
        {
            using (var db = new QDMallDatabase())
            {
                return db.Page<ClientView>(page, pageSize, string.Format("select Id,staffid,Tags,Source,Probability,uid,FollowTime from crm_Client where staffid={0}", staffid));
            }
        }

        public static Page<ClientView> GetCllientByProbability(int probability, int staffid, int page, int pageSize)
        {
            var sb = new StringBuilder();
            if (probability == 1)
            {
                sb.AppendFormat("select * from crm_Client where Staffid={0} order by Probability desc", staffid);
            }
            else if (probability == 2)
            {
                sb.AppendFormat("select * from crm_Client where Staffid={0} order by followTime desc", staffid);
            }
            else if(probability==3)
            {
                sb.AppendFormat("select * from crm_Client where Staffid={0} order by ActiveTime desc", staffid);
            }
            using (var db = new QDMallDatabase())
            {
                return db.Page<ClientView>(page, pageSize, sb.ToString());
            }
        }
        public static Page<ClientView> GetCllientBySearch(int staffid, string search, int page, int pageSize)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("select * from crm_Client where staffid={0} and nickname like '%{1}%'", staffid, search);

            using (var db = new QDMallDatabase())
            {
                return db.Page<ClientView>(page, pageSize, sb.ToString());
            }
        }

        public static Page<ClientView> GetCllientByTags(int staffid, int tags, int page, int pageSize)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("select * from crm_Client where Staffid={0} and  Tags={1}", staffid, tags);

            using (var db = new QDMallDatabase())
            {
                return db.Page<ClientView>(page, pageSize, sb.ToString());
            }
        }

        public static Page<ClientView> GetCllientBySource(int staffid, int source, int page, int pageSize)
        {
            var sb = new StringBuilder();
            sb.AppendFormat("select * from crm_Client where Staffid={0} and  Source={1}", staffid, source);

            using (var db = new QDMallDatabase())
            {
                return db.Page<ClientView>(page, pageSize, sb.ToString());
            }
        }
        /// <summary>
        /// 获取未添加的标签客户
        /// </summary>
        /// <param name="staffId"></param>
        /// <param name="tagId"></param>
        /// <param name="page"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public static Page<ClientTagRelationView> GetClientListNotIn(int staffId, int tagId, int page, int pageSize)
        {
            var sql = string.Format("select id,staffId,Uid,MemoName from crm_Client where staffid={0} and id not in (select Clientid from crm_ClientRelations where staffId={0} and ClientTagid={1})", staffId, tagId);
            using (var db = new QDMallDatabase())
            {
                return db.Page<ClientTagRelationView>(page, pageSize, sql);
            }
        }

        /// <summary>
        /// 获取所有名片的ID
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public static List<Clients> GetListByUid(int uid)
        {
            using (var db = new QDMallDatabase())
            {
                return db.Fetch<Clients>(string.Format("select Id,Staffid,Uid,isnull(DisplayState,1) as DisplayState,Source,addtime from crm_Client where uid={0}", uid));
            }
        }

        /// <summary>
        /// 获取单条数据
        /// </summary>
        /// <param name="Msgid"></param>
        /// <param name="IsCheck"></param>
        /// <returns></returns>
        public static Clients Get(int id)
        {
            using (var db = new QDMallDatabase())
            {
                string sql = string.Format("select top 1 * from  crm_Client  where id={0}", id);
                return db.SingleOrDefault<Clients>(sql, id);
            }

        }
        public static Clients GetByUid(int uid,int staffId)
        {
            using (var db = new QDMallDatabase())
            {
                return db.SingleOrDefault<Clients>(string.Format("select top 1 * from crm_Client where uid={0} and staffId={1}", uid, staffId));
            }
        }

        //修改客户表里的标签信息
        public static int UpdateTagName(string TagName, int id)
        {
            using (var db = new QDMallDatabase())
            {
                string sql = string.Format("update crm_Client set Tags='{0}' where id={1}", TagName, id);
                return db.Execute(sql);
            }
        }
        public static int UpdateTradeDate(int clientId, string updatetime)
        {
            var uptime = updatetime.Replace(',', '-');
            int count = 0;
            using (var db = new QDMallDatabase())
            {
                string sql = string.Format("update crm_Client set tradeDate ='{0}' where id={1}", uptime, clientId);
                count = db.Execute(sql);
            }
            return count > 0 ? 0 : 1;
        }

        public static int UpdateProbability(int uid, int probability)
        {
            int count = 0;
            using (var db = new QDMallDatabase())
            {
                string sql = string.Format("update crm_Client set Probability = {0} where id={1}", probability, uid);
                count = db.Execute(sql);
            }
            return count > 0 ? 0 : 1;
        }

        public static bool UpdateTags(int uid, int tags)
        {
            int count = 0;
            using (var db = new QDMallDatabase())
            {
                count = db.Execute("update crm_Client set Tags={0} where id={1}", tags, uid);
            }
            return count > 0 ? true : false;
        }

        /// <summary>
        /// 修改名片状态
        /// </summary>
        /// <param name="staffId">员工ID</param>
        /// <param name="uid">客户的uid</param>
        /// <param name="displayState">名片状态(1 显示, 2 屏蔽)</param>
        /// <returns></returns>
        public static bool UpdateDisplayState(int staffId, int uid, int displayState)
        {
            int count = 0;
            using (var db = new QDMallDatabase())
            {
                count = db.Execute(string.Format("update crm_Client set DisplayState={0} where staffId={1} and uid={2}", displayState, staffId, uid));
            }
            return count > 0 ? true : false;
        }

        /// <summary>
        /// 修改客户来源
        /// </summary>
        /// <param name="staffId"></param>
        /// <param name="uid"></param>
        /// <param name="source"></param>
        /// <returns></returns>
        public static bool UpdateSource(int staffId, int uid, int source)
        {
            int count = 0;
            using (var db = new QDMallDatabase())
            {
                count = db.Execute(string.Format("update crm_Client set source={0} where staffId={1} and uid={2}", source, staffId, uid));
            }
            return count > 0 ? true : false;
        }

        public static bool UpdateFollowTime(int id)
        {
            int count = 0;
            using (var db = new QDMallDatabase())
            {
                count = db.Execute(string.Format("update crm_Client set followTime=GETDATE() where id={0}",id));
            }
            return count > 0 ? true : false;
        }


        public static int Update(int StaffId, int NewStaffId,int Source) {
            using (var db = new QDMallDatabase())
            {//更改为已交过
                return db.Execute(string.Format("update crm_Client set Source={0} where staffId={1};update crm_Client set staffId={2} where staffId={1}"
                    , Source,StaffId, NewStaffId));
            }
        }

        public static bool Update(Clients model)
        {
            using (var db = new QDMallDatabase())
            {
                return db.Update(model) > 0;
            }
        }


        #region boos 统计

        /// <summary>
        /// 客户总数
        /// </summary>
        /// <param name="staffIds">成员ID列表</param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static int GetTotalClient(string staffIds, string startTime, string endTime)
        {
            var sb = new StringBuilder();
            sb.Append("select count(1) as clientCount from crm_Client where 1=1 ");

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and Addtime between '{0}' and '{1}'", startTime, endTime);

            if (!string.IsNullOrEmpty(staffIds))
                sb.AppendFormat(" and staffId in ({0}) ", staffIds);

            using (var db = new QDMallDatabase())
            {
                return db.ExecuteScalar<int>(sb.ToString());
            }
        }

        /// <summary>
        /// 成交率列表
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<ProbabilityView> GetProbabilityList(string staffIds, string startTime, string endTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select id,StaffId,Probability from crm_Client where ");

            sb.AppendFormat(" staffId in ({0})", staffIds);

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and Addtime between '{0}' and '{1}'", startTime, endTime);

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<ProbabilityView>(sb.ToString());
            }
        }

        /// <summary>
        ///  boss汇总-客户数统计，按天统计
        /// </summary>
        /// <param name="departmentIds">员工ID</param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<DayTotalView> GetClientByDate(string staffIds, string startTime, string endTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select convert(date,addtime) as [date],count(1) as total from crm_Client where 1=1 ");

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and Addtime between '{0}' and '{1}'", startTime, endTime);

            if (!string.IsNullOrEmpty(staffIds))
                sb.AppendFormat(" and staffId in ({0})", staffIds);

            sb.Append(" group by convert(date,Addtime)");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<DayTotalView>(sb.ToString());
            }
        }

        /// <summary>
        ///  boss汇总-跟进数统计，按天统计
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<DayTotalView> GetFollowByDate(string staffIds, string startTime, string endTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select convert(date,addtime) as [date],count(1) as total from crm_ClientFollow where 1=1 ");

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and Addtime between '{0}' and '{1}'", startTime, endTime);

            if (!string.IsNullOrEmpty(staffIds))
                sb.AppendFormat(" and staffId in ({0})", staffIds);

            sb.Append(" group by convert(date,Addtime)");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<DayTotalView>(sb.ToString());
            }
        }

        #endregion

        #region AI数据分析
        ///// <summary>
        ///// boss汇总-客户总数
        ///// </summary>
        ///// <param name="departmentIds"></param>
        ///// <param name="startTime"></param>
        ///// <param name="endTime"></param>
        ///// <returns></returns>
        //public static int GetTotalClient_old(string departmentIds, string startTime, string endTime)
        //{
        //    var sb = new StringBuilder();
        //    sb.Append("select SUM(counts) as clientCount from(");
        //    sb.Append("select id as staffId, (select count(1) from crm_Client c where c.staffId = crm_Staff.id ");

        //    if (!string.IsNullOrEmpty(startTime))
        //        sb.AppendFormat("and c.Addtime between '{0}' and '{1}'", startTime, endTime);

        //    sb.Append(") as counts from crm_Staff");

        //    if (!string.IsNullOrEmpty(departmentIds))
        //        sb.AppendFormat("  where DepartmentId in (select DepartmentId from crm_StaffDepartment where departmentid in({0}) )", departmentIds);

        //    sb.Append(") as t");

        //    using (var db = new QDMallDatabase())
        //    {
        //        return db.ExecuteScalar<int>(sb.ToString());
        //    }
        //}
        ///// <summary>
        ///// boss汇总-成交率漏斗客户
        ///// </summary>
        ///// <param name="departmentIds"></param>
        ///// <param name="startTime"></param>
        ///// <param name="endTime"></param>
        ///// <returns></returns>
        //public static List<ProbabilityView> GetProbabilityList_old(string departmentIds, string startTime, string endTime)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("select crm_Client.id,crm_Client.staffId,crm_Client.Probability from crm_Client,crm_Staff where crm_Client.staffId = crm_Staff.id ");

        //    if (!string.IsNullOrEmpty(startTime))
        //        sb.AppendFormat(" and crm_Client.Addtime between '{0}' and '{1}'", startTime, endTime);

        //    if (!string.IsNullOrEmpty(departmentIds))
        //        sb.AppendFormat(" and crm_Staff.DepartmentId in ({0})", departmentIds);

        //    using (var db = new QDMallDatabase())
        //    {
        //        return db.Fetch<ProbabilityView>(sb.ToString());
        //    }
        //}

        ///// <summary>
        /////  boss汇总-返回最近客户列表，按日期汇总
        ///// </summary>
        ///// <param name="departmentIds"></param>
        ///// <param name="startTime"></param>
        ///// <param name="endTime"></param>
        ///// <returns></returns>
        //public static List<DayTotalView> GetDayTotalList(string departmentIds, string startTime, string endTime)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    sb.Append("select addtime,count(1) as counts from (select crm_Client.id,crm_Client.addtime from crm_Client,crm_Staff where crm_Client.staffId = crm_Staff.id ");

        //    if (!string.IsNullOrEmpty(startTime))
        //        sb.AppendFormat(" and crm_Client.Addtime between '{0}' and '{1}'", startTime, endTime);

        //    if (!string.IsNullOrEmpty(departmentIds))
        //        sb.AppendFormat(" and crm_Staff.DepartmentId in ({0})", departmentIds);

        //    sb.Append(") as t group by addtime");

        //    using (var db = new QDMallDatabase())
        //    {
        //        return db.Fetch<DayTotalView>(sb.ToString());
        //    }
        //}

        public static List<SalesTopView> GetTotalReport(string departmentIds, string startTime, string endTime)
        {
            var sb = new StringBuilder();
            sb.Append("select * from (select id as staffId,Uid,Avater,Name,(select count(1) from crm_Client c where c.staffId = crm_Staff.id ");

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and c.addTime between '{0}' and '{1}'", startTime, endTime);

            sb.Append(") as counts from crm_Staff ");

            if (!string.IsNullOrEmpty(departmentIds))
            {
                if (departmentIds.Contains(","))
                    sb.AppendFormat(" where DepartmentId in (select DepartmentId from crm_StaffDepartment where departmentid in({0}))", departmentIds);
                else
                    sb.AppendFormat(" where DepartmentId={0}", departmentIds);
            }

            sb.Append(") as t order by counts desc");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<SalesTopView>(sb.ToString());
            }
        }
        /// <summary>
        /// 销售排行-按跟进数倒叙
        /// </summary>
        /// <param name="departmentIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<SalesTopView> GetSalesListByFollow(string departmentIds, string startTime, string endTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from (select id as staffId,uid,Avater,Name, (select COUNT(1) from crm_ClientFollow c where c.StaffId = crm_Staff.id ");

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and c.addTime between '{0}' and '{1}'", startTime, endTime);

            sb.Append(") as counts from crm_Staff ");

            if (!string.IsNullOrEmpty(departmentIds))
            {
                if (departmentIds.Contains(","))
                    sb.AppendFormat(" where DepartmentId in (select DepartmentId from crm_StaffDepartment where departmentid in({0}))", departmentIds);
                else
                    sb.AppendFormat(" where DepartmentId={0}", departmentIds);
            }

            sb.Append(") as t order by counts desc");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<SalesTopView>(sb.ToString());
            }
        }

        /// <summary>
        /// 销售排行-按沟通数倒叙
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="actionType"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<SalesTopView> GetSalesListByIM(string departmentIds, string startTime, string endTime)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select * from (select id as staffId,uid,Avater,Name, (select count(1) from  crm_actionRecord c where c.parentUid=crm_Staff.uid ");

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and c.addTime between '{0}' and '{1}'", startTime, endTime);

            sb.Append(") as counts from crm_Staff ");

            if (!string.IsNullOrEmpty(departmentIds))
            {
                if (departmentIds.Contains(","))
                    sb.AppendFormat(" where DepartmentId in (select DepartmentId from crm_StaffDepartment where departmentid in({0}))", departmentIds);
                else
                    sb.AppendFormat(" where DepartmentId={0}", departmentIds);
            }

            sb.Append(") as t order by counts desc");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<SalesTopView>(sb.ToString());
            }
        }

        /// <summary>
        /// 销售排行-按客户数排序
        /// </summary>
        /// <param name="staffIds"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public static List<SalesTopView> GetSalesListByClient(string departmentIds, string startTime, string endTime)
        {
            var sb = new StringBuilder();
            sb.Append("select * from (select id as staffId,Uid,Avater,Name,(select count(1) from crm_Client c where c.staffId = crm_Staff.id ");

            if (!string.IsNullOrEmpty(startTime))
                sb.AppendFormat(" and c.addTime between '{0}' and '{1}'", startTime, endTime);

            sb.Append(") as counts from crm_Staff ");

            if (!string.IsNullOrEmpty(departmentIds))
            {
                if (departmentIds.Contains(","))
                    sb.AppendFormat(" where DepartmentId in (select DepartmentId from crm_StaffDepartment where departmentid in({0}))", departmentIds);
                else
                    sb.AppendFormat(" where DepartmentId={0}", departmentIds);
            }

            sb.Append(") as t order by counts desc");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<SalesTopView>(sb.ToString());
            }
        }

        /// <summary>
        /// 销售排行-成交率倒序
        /// </summary>
        /// <param name="departmentIds"></param>
        /// <param name="minProbability"></param>
        /// <param name="maxProbability"></param>
        /// <returns></returns>
        public static List<SalesTopView> GetSalesListByProbability(string departmentIds, int minProbability, int maxProbability)
        {
            var sb = new StringBuilder();
            sb.Append("select * from (select id as staffId,Uid,Avater,Name,(select count(1) from crm_Client c where c.staffId = crm_Staff.id ");

            if (minProbability > 0)
                sb.AppendFormat(" and c.Probability between {0} and {1}", minProbability, maxProbability);

            sb.Append(") as counts from crm_Staff ");

            if (!string.IsNullOrEmpty(departmentIds))
            {
                if (departmentIds.Contains(","))
                    sb.AppendFormat(" where DepartmentId in (select DepartmentId from crm_StaffDepartment where departmentid in({0}))", departmentIds);
                else
                    sb.AppendFormat(" where DepartmentId={0}", departmentIds);
            }

            sb.Append(") as t order by counts desc");

            using (var db = new QDMallDatabase())
            {
                return db.Fetch<SalesTopView>(sb.ToString());
            }
        }

        #endregion
    }
}
